﻿/*成单待收款查询*/
CREATE PROCEDURE [dbo].[proc_Order_Getlist_Claim_G20141229]
	(
		@CompanyId int,		
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int,
		@StartDate nchar(10),
		@EndDate nchar(10)
	)
AS
Begin

declare @sql nvarchar(4000),@dcsql nvarchar(1000)

set @sql=''
set @dcsql=''
if @CompanyId<>0
begin
	set @sql=@sql+' CompanyId='+Convert(varchar,@CompanyId)+' and '
end
if @Stext<>''
begin
	set @sql=@sql+' CusName like ''%'+@Stext+'%'' and '
	set @dcsql=@dcsql+' o.CusName like ''%'+@Stext+'%'' and '
end

set @sql='
declare @sRq DATETIME,@eRq datetime
set @sRq=Convert(datetime,'''+@StartDate+' 00:00:00'')
set @eRq=Convert(datetime,'''+@EndDate+' 23:59:59'')


	;WITH list As(
	select ROW_NUMBER() OVER (ORDER BY CreateDate )AS Row,* from (
	Select  
		Id
		,OId
		,IsNull(OrderId,'''') As OrderId
		,CusId
		,CusName		
		,Salesman		
		,CreateDate
		,YingShouPrice
		,YingShouPrice-ShiShouPrice	as daishou	
		,flag
		,NOE_Flag,typename		
		From [Order]
		Where  '+@sql+' StateId>=1 and YingShouPrice-ShiShouPrice>0 AND NOE_Flag<>2 AND AuditDate>=@sRq and AuditDate<=@eRq And FinanceRenlingDate is not null
	
	--union 
	--SELECT  o.Id,o.oid,IsNull(o.OrderId,'''') As OrderId,o.CusId,o.CusName,o.Salesman,UpTime,
	--o.YingShouPrice,dch_ysh,0,o.NOE_Flag,o.typename FROM Order_Change_Hedge 
	--inner join [Order] o on Order_Change_Hedge.oid =o.oid 
	-- and Order_Change_Hedge.CompanyId='+Convert(varchar,@CompanyId)+' and UpTime>=@sRq and UpTime<=@eRq  
	-- and o.companyId='+Convert(varchar,@CompanyId)+' AND '+@dcsql+' o.NOE_Flag<>2 and dch_ysh>0
	) as nb1 	
	)
	
	Select *,(SELECT COUNT(1) FROM list) AS cid,(select sum(YingShouPrice) from list) as sum_ys,
	(select sum(daishou) from list) as sum_ds From list  Where Row 
	Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row
	'
	
	exec(@sql)
	--print (@sql)
End
